﻿//using System;
//using System.Collections.Generic;
//using System.Linq;
//using System.Text;
//using SmartSpace.Core.Metadata;
//using SmartSpace.Core.Data;
//using System.Data.Common;
//using SmartSpace.Core.Metadata.Workflow;

//namespace SmartSpace.Data.Util
//{
//    public class DbScriptUtil
//    {
//        public static string GetScript(EntityMetadata entity)
//        {
//            StringBuilder sqlBuilder = new StringBuilder();
//            sqlBuilder.AppendFormat("if exists (select 1 from  sysobjects where  id = object_id('{0}') and   type = 'U')",
//                GetTableName(entity));
//            sqlBuilder.AppendLine();
//            sqlBuilder.AppendFormat("drop table {0};", GetTableName(entity));
//            sqlBuilder.AppendLine();
//            //sqlBuilder.Append("go");
//            sqlBuilder.AppendLine();
//            sqlBuilder.AppendFormat("CREATE TABLE {0}", GetTableName(entity));
//            sqlBuilder.AppendLine();
//            sqlBuilder.Append("(");
//            sqlBuilder.AppendLine();

//            for (int index = 0; index < entity.Properties.Count; index++)
//            {
//                PropertyMetadata property = entity.Properties[index];
//                // name type null
//                sqlBuilder.AppendFormat("{0} {1} {2},",
//                    GetPropertyName(property),
//                    GetPropertyType(property),
//                    GetPropertyNullable(property));

//                sqlBuilder.AppendLine();
//            }
//            sqlBuilder.AppendFormat("CONSTRAINT {0} PRIMARY KEY CLUSTERED (", GetPrimaryKeyName(entity));
//            for (int index = 0; index < entity.Properties.Count; index++)
//            {
//                PropertyMetadata property = entity.Properties[index];
//                if (property.IsIdentity)
//                {
//                    sqlBuilder.AppendLine();
//                    sqlBuilder.AppendFormat("{0},", GetPropertyName(property));
//                }
//            }
//            sqlBuilder = sqlBuilder.Remove(sqlBuilder.Length - 1, 1);
//            sqlBuilder.AppendLine();
//            sqlBuilder.AppendLine(")) ON [PRIMARY]");

//            return sqlBuilder.ToString();
//        }

//        public static string GetInsertScript(DetailData data, out List<object> parameters)
//        {
//            StringBuilder sqlBuilder = new StringBuilder();
//            parameters = new List<object>();

//            sqlBuilder.AppendFormat("INSERT INTO {0} ", GetTableName(data.Entity));
//            StringBuilder pnameBuilder = new StringBuilder();
//            StringBuilder pBuilder = new StringBuilder();
//            for (int index = 0; index < data.Fields.Count; index++)
//            {
//                FieldInstance field = data.Fields[index];

//                pnameBuilder.AppendFormat("{0}", GetPropertyName(field.Property));
//                pBuilder.Append("{" + index.ToString() + "}");
//                parameters.Add(field.GetOriginalValue());

//                if (index < data.Fields.Count - 1)
//                {
//                    pnameBuilder.Append(",");
//                    pBuilder.Append(",");
//                }
//            }
//            sqlBuilder.AppendFormat("({0}) VALUES ({1})", pnameBuilder, pBuilder);

//            return sqlBuilder.ToString();
//        }

//        public static string GetUpdateScript(DetailData data, out List<object> parameters)
//        {
//            StringBuilder sqlBuilder = new StringBuilder();
//            parameters = new List<object>();

//            sqlBuilder.AppendFormat("UPDATE {0} SET ", GetTableName(data.Entity));
//            StringBuilder pnameBuilder = new StringBuilder();
//            StringBuilder pBuilder = new StringBuilder();
//            int idIndex = 0;
//            for (int index = 0; index < data.Fields.Count; index++)
//            {
//                FieldInstance field = data.Fields[index];
//                if (!field.Property.IsIdentity)
//                {
//                    sqlBuilder.AppendFormat("{0}=", GetPropertyName(field.Property));
//                    sqlBuilder.Append("{" + index.ToString() + "},");
//                }
//                else
//                {
//                    idIndex = index;
//                }
//                parameters.Add(field.GetOriginalValue());
//            }
//            if (parameters.Count > 0)
//            {
//                sqlBuilder = sqlBuilder.Remove(sqlBuilder.Length - 1, 1);
//            }
//            sqlBuilder.AppendFormat(" WHERE {0}=", GetPropertyName(data.GetIdentity().Property));
//            sqlBuilder.Append("{" + idIndex.ToString() + "}");

//            return sqlBuilder.ToString();
//        }

//        public static string GetExistScript(DetailData data, out List<object> parameters)
//        {
//            StringBuilder sqlBuilder = new StringBuilder();
//            parameters = new List<object>();

//            sqlBuilder.AppendFormat("SELECT COUNT({1}) as Count FROM {0} where {1} = ",
//                GetTableName(data.Entity),
//                GetPropertyName(data.GetIdentity().Property));
//            sqlBuilder.Append("{0}");

//            parameters.Add(data.GetIdentity().GetOriginalValue());

//            return sqlBuilder.ToString();
//        }

//        public static string GetExistScript(DetailData data, DbCommand command, out List<DbParameter> parameters)
//        {
//            StringBuilder sqlBuilder = new StringBuilder();
//            parameters = new List<DbParameter>();

//            sqlBuilder.AppendFormat("SELECT {1} FROM {0} where {1} = @p1",
//                GetTableName(data.Entity),
//                GetPropertyName(data.GetIdentity().Property));

//            DbParameter parameter = command.CreateParameter();
//            parameter.ParameterName = "@p1";
//            parameter.Value = data.GetIdentity().GetOriginalValue();
//            parameters.Add(parameter);

//            return sqlBuilder.ToString();
//        }

//        public static string GetPagedListScript(ViewMetadata view, int start, int limit, out List<DbParameter> parameters)
//        {
//            StringBuilder sqlBuilder = new StringBuilder();
//            parameters = new List<DbParameter>();

//            StringBuilder queryBuilder = new StringBuilder();
//            StringBuilder conditionBuilder = new StringBuilder();

//            for (int index = 0; index < view.Fields.Count; index++)
//            {
//                SmartSpace.Core.Metadata.Workflow.FieldMetadata field = view.Fields[index];
//                if (field.IsList || field.Property.IsIdentity)
//                {
//                    queryBuilder.AppendFormat("{0},", GetPropertyName(field.Property));
//                }
//            }
//            queryBuilder = queryBuilder.Remove(queryBuilder.Length - 1, 1);
//            sqlBuilder.AppendFormat(
//                "select {0} from (select {0},row_number() over(order BY {1}) rowIndex from {2}) k where rowIndex between {3} and {4}",
//                queryBuilder,
//                GetPropertyName(view.Entity.GetIdentity()),
//                GetTableName(view.Entity),
//                start, // 1-25, 26-50
//                start + limit - 1); 

//            return sqlBuilder.ToString();
//        }

//        public static string GetPagedListScript(ActivityMetadata activity, DbCommand command, int start, int limit, out List<DbParameter> parameters)
//        {
//            StringBuilder sqlBuilder = new StringBuilder();
//            parameters = new List<DbParameter>();

//            StringBuilder queryBuilder = new StringBuilder();
//            StringBuilder queryTempBuilder = new StringBuilder();
//            StringBuilder conditionBuilder = new StringBuilder();

//            for (int index = 0; index < activity.ModuleView.Fields.Count; index++)
//            {
//                SmartSpace.Core.Metadata.Workflow.FieldMetadata field = activity.ModuleView.Fields[index];
//                if (field.IsList || field.Property.IsIdentity)
//                {
//                    queryBuilder.AppendFormat("{0},", GetPropertyName(field.Property));
//                    queryTempBuilder.AppendFormat("ent.{0},", GetPropertyName(field.Property));
//                }
//            }
//            queryBuilder = queryBuilder.Remove(queryBuilder.Length - 1, 1);

//            if (activity.IsRoot)
//            {
//                sqlBuilder.AppendFormat(
//                    "select {0} from (select {1} row_number() over(order BY ent.{2}) rowIndex from {3} as ent where not exists (select wi.dataid from __WorkflowInstance as wi where wi.DataId=ent.{2})) as k where rowIndex between {4} and {5}",
//                    queryBuilder,
//                    queryTempBuilder,
//                    GetPropertyName(activity.ModuleView.Entity.GetIdentity()),
//                    GetTableName(activity.ModuleView.Entity),
//                    start, // 1-25, 26-50
//                    start + limit - 1);
//            }
//            else
//            {
//                DbParameter activityIdParam = command.CreateParameter();
//                activityIdParam.ParameterName = "@p1";
//                activityIdParam.Value = activity.Id;
//                parameters.Add(activityIdParam);

//                sqlBuilder.AppendFormat(
//                    "select {0} from (select {1} row_number() over(order BY ent.{2}) rowIndex from {3} as ent where exists (select wi.dataid from __WorkflowInstance as wi where wi.DataId=ent.{2} and wi.ActivityId=@p1)) as k where rowIndex between {4} and {5}",
//                    queryBuilder,
//                    queryTempBuilder,
//                    GetPropertyName(activity.ModuleView.Entity.GetIdentity()),
//                    GetTableName(activity.ModuleView.Entity),
//                    start, // 1-25, 26-50
//                    start + limit - 1);
//            }

//            return sqlBuilder.ToString();
//        }

//        public static string GetSelectScript(EntityMetadata entity, Guid identity, DbCommand command, out List<DbParameter> parameters)
//        {
//            StringBuilder sqlBuilder = new StringBuilder();
//            parameters = new List<DbParameter>();

//            StringBuilder queryBuilder = new StringBuilder();
//            StringBuilder conditionBuilder = new StringBuilder();

//            for (int index = 0; index < entity.Properties.Count; index++)
//            {
//                SmartSpace.Core.Metadata.PropertyMetadata property = entity.Properties[index];
//                queryBuilder.AppendFormat("{0},", GetPropertyName(property));
//            }
//            queryBuilder = queryBuilder.Remove(queryBuilder.Length - 1, 1);
//            sqlBuilder.AppendFormat(
//                "select {0} from {1} where {2} =@p1",
//                queryBuilder,
//                GetTableName(entity),
//                GetPropertyName(entity.GetIdentity()));

//            DbParameter identityParam = command.CreateParameter();
//            identityParam.ParameterName = "@p1";
//            identityParam.Value = identity;
//            parameters.Add(identityParam);

//            return sqlBuilder.ToString();
//        }

//        /// <summary>
//        /// 获取获得数据总数的 sql语句
//        /// </summary>
//        /// <param name="entity"></param>
//        /// <returns></returns>
//        public static string GetTotalScript(EntityMetadata entity)
//        {
//            StringBuilder sqlBuilder = new StringBuilder();
//            sqlBuilder.AppendFormat("SELECT COUNT({1}) as Count FROM {0}",
//                GetTableName(entity),
//                GetPropertyName(entity.GetIdentity()));
//            return sqlBuilder.ToString();
//        }

//        public static string GetTotalScript(ActivityMetadata activity, DbCommand command, out List<DbParameter> parameters)
//        {
//            StringBuilder sqlBuilder = new StringBuilder();
//            parameters = new List<DbParameter>();

//            if (activity.IsRoot)
//            {
//                sqlBuilder.AppendFormat(
//                    "select COUNT({0}) as Count from {1} as ent where not exists (select wi.dataid from __WorkflowInstance as wi where wi.DataId=ent.{0})",
//                    GetPropertyName(activity.ModuleView.Entity.GetIdentity()),
//                    GetTableName(activity.ModuleView.Entity));
//            }
//            else
//            {
//                DbParameter activityIdParam = command.CreateParameter();
//                activityIdParam.ParameterName = "@p1";
//                activityIdParam.Value = activity.Id;
//                parameters.Add(activityIdParam);

//                sqlBuilder.AppendFormat(
//                    "select COUNT({0}) as Count from {1} as ent where exists (select wi.dataid from __WorkflowInstance as wi where wi.DataId=ent.{0} and wi.ActivityId=@p1)",
//                    GetPropertyName(activity.ModuleView.Entity.GetIdentity()),
//                    GetTableName(activity.ModuleView.Entity));
//            }

//            return sqlBuilder.ToString();
//        }

//        private static string GetTableName(EntityMetadata entity)
//        {
//            return string.Format("[{0}]", entity.Name);
//        }

//        private static string GetPrimaryKeyName(EntityMetadata entity)
//        {
//            return string.Format("[PK_{0}]", entity.Name);
//        }

//        public static string GetPropertyName(PropertyMetadata property)
//        {
//            return string.Format("[{0}]", property.Name);
//        }

//        private static string GetPropertyType(PropertyMetadata property)
//        {
//            TypeMetadata type = property.Type;
//            if (property.MaxLength != 0)
//            {
//                return string.Format("[{0}]({1})", type.DbType, property.MaxLength);
//            }
//            else
//            {
//                return string.Format("[{0}]", type.DbType);
//            }
//        }

//        private static string GetPropertyNullable(PropertyMetadata property)
//        {
//            if (property.IsNullable)
//            {
//                return "NULL";
//            }
//            else
//            {
//                return "NOT NULL";
//            }
//        }
//    }
//}
